package com.anolesoft.amljmes.fortysix.service.mapper;

import cn.hutool.core.util.StrUtil;
import com.anolesoft.amljmes.fortysix.controller.MesBzzZcbldInfoController;
import com.anolesoft.amljmes.fortysix.dto.MesBzzBindDto;
import com.anolesoft.amljmes.fortysix.dto.MesBzzZcbldDto;
import com.anolesoft.amljmes.fortysix.dto.MesBzzZcbldmxDto;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.jdbc.SQL;

import java.util.List;

@Mapper
public interface MesBzzZcbldInfoServiceMapper {
    @SelectProvider(type=MesBzzZcbldInfoServiceMapper.createSQL.class, method = "queryMesBzzZcbldSQL")
    List<MesBzzZcbldDto> queryMesBzzZcbldSQL(MesBzzZcbldInfoController.DemoVO demoVO);
    public static class createSQL {
        public static String queryMesBzzZcbldSQL(MesBzzZcbldInfoController.DemoVO demoVO){
            return new SQL(){
                {
                    StringBuilder sql = new StringBuilder();
                    sql.append(" a.objuid              as key,\n" +
                            "       a.objuid,\n" +
                            "       a.baoliudanhao,\n" +
                            "       a.model,\n" +
                            "       a.jiaci,\n" +
                            "       a.ptdid,\n" +
                            "       a.yijiaodanwei,\n" +
                            "       e.name                yijiaoren,\n" +
                            "       a.jieshoudanwei,\n" +
                            "       a.jieshoudaibiao,\n" +
                            "       a.creat_time,\n" +
                            "       a.factory_code,\n" +
                            "       a.state,\n" +
                            "       c.name creat_name,\n" +
                            "       b.supporting_order_no,\n" +
                            "       b.order_number,\n" +
                            "       b.order_number,\n" +
                            "       b.fathernumber,\n" +
                            "       b.fathername,\n" +
                            "       b.proportion,\n" +
                            "       b.batch," +
                            "       0                     as pageNo,\n" +
                            "       10                    as pageSize\n" +
                            "  from mes_bzz_zcbld a\n" +
                            "  left join mes_bzz_ptd b\n" +
                            "    on a.ptdid = b.objuid\n" +
                            "  left join stf_employee e\n" +
                            "    on a.yijiaoren = e.objuid\n" +
                            "  left join stf_employee c\n" +
                            "    on a.creat_name = c.eid and a.factory_code=c.factory_id\n" +
                            " where a.state = 0");
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getBaoliudanhao())){
                        String baoliudanhao = demoVO.getBaoliudanhao();
                        sql.append(" and upper(a.baoliudanhao) = trim(upper(#{baoliudanhao, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getOrderNumber())){
                        String orderNumber = demoVO.getOrderNumber();
                        sql.append(" and upper(b.order_number) = trim(upper(#{orderNumber, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getSupportingOrderNo())){
                        String supportingOrderNo = demoVO.getSupportingOrderNo();
                        sql.append(" and upper(b.supporting_order_no) = trim(upper(#{supportingOrderNo, jdbcType=VARCHAR}))");
                    }
                    if(!StrUtil.isEmptyOrUndefined(demoVO.getFactoryCode())){
                        String factoryCode = demoVO.getFactoryCode();
                        sql.append(" and upper(a.factory_code) = upper(#{factoryCode, jdbcType=VARCHAR})");
                    }
                    SELECT(sql.toString());
                }
            }.toString();
        }
    }

//    @Select("select b.*,a.sorties,c.shuliang,a.state junjian,a.supporting_order_no " +
//            "from mes_bzz_ptd a, plan_goods_bind b, mes_bzz_fcfhjh c " +
//            "where a.objuid = b.ptd_obj " +
//            "and b.hegebiaoqian = c.qualified_label_number " +
//            "and a.state = 2 " +
//            "or a.state = 3 " +
//            "and b.jianyanstate = 0 " +
//            "and b.state = 0 " +
//            "and a.supporting_order_no = #{supportingOrderNo, jdbcType=VARCHAR}")
//    @Select("select b.*,\n" +
//            "       a.state junjian,\n" +
//            "       a.supporting_order_no,\n" +
//            "       a.dispatch,\n" +
//            "       a.fathernumber,\n" +
//            "       a.fathername,\n" +
//            "       a.proportion,\n" +
//            "       a.affiliation\n" +
//            "  from plan_goods_bind b\n" +
//            "  left join mes_bzz_ptd a\n" +
//            "    on a.objuid = b.ptd_obj\n" +
////            "  left join mes_bzz_fcfhjh c\n" +
////            "    on b.hegebiaoqian = c.qualified_label_number\n" +
//            " where a.state in(2,3)\n" +
//            "   and b.jianyanstate = 0\n" +
//            "   and b.state = 4\n" +
////            "   and c.state != 3\n" +
//            "   and a.supporting_order_no = #{peitaodanhao, jdbcType=VARCHAR}")

    @Select("select t.objuid,\n" +
            "       t.ptdmx_obj,\n" +
            "       t.ptd_obj,\n" +
            "       t.paigonghao,\n" +
            "       t.jixing,\n" +
            "       t.tuhao,\n" +
            "       t.jiaofuchejian,\n" +
            "       t.jieshouchejian,\n" +
            "       t.jiaofushunxu,\n" +
            "       t.hegebiaoqian,\n" +
            "       t.bangdingnum,\n" +
            "       a.name                bangdingren,\n" +
            "       t.bangdingtime,\n" +
            "       t.xianghao,\n" +
            "       t.dingdanbianhao,\n" +
            "       b.name                tijiaojianyanren,\n" +
            "       t.tijiaojianyantime,\n" +
            "       t.jianyanstate,\n" +
            "       c.name                jianyanren,\n" +
            "       t.jianyantime,\n" +
            "       t.yijiaoren,\n" +
            "       t.yijiaotime,\n" +
            "       t.peitaodanhao,\n" +
            "       t.yijiaodanhao,\n" +
            "       t.bujiandanhao,\n" +
            "       t.kucun_obj,\n" +
            "       t.state,\n" +
            "       t.factory_id,\n" +
            "       t.danwei,\n" +
            "       t.danjishu,\n" +
            "       t.pici,\n" +
            "       t.jiaci,\n" +
            "       d.name                shenpiren,\n" +
            "       t.shenpitime,\n" +
            "       e.name                tishenren,\n" +
            "       t.tishentime,\n" +
            "       f.name                chehiuren,\n" +
            "       t.chehiutime,\n" +
            "       z.state               junjian,\n" +
            "       z.supporting_order_no,\n" +
            "       z.dispatch,\n" +
            "       z.fathernumber,\n" +
            "       z.fathername,\n" +
            "       z.proportion,\n" +
            "       z.affiliation\n" +
            "  from plan_goods_bind t\n" +
            "  left join stf_employee a\n" +
            "    on t.bangdingren = a.objuid\n" +
            "  left join stf_employee b\n" +
            "    on t.tijiaojianyanren = b.objuid\n" +
            "  left join stf_employee c\n" +
            "    on t.jianyanren = c.objuid\n" +
            "  left join stf_employee d\n" +
            "    on t.shenpiren = d.objuid\n" +
            "  left join stf_employee e\n" +
            "    on t.tishenren = e.objuid\n" +
            "  left join stf_employee f\n" +
            "    on t.chehiuren = f.objuid\n" +
            "  left join mes_bzz_ptd z\n" +
            "    on z.objuid = t.ptd_obj\n" +
            " where z.state in (2, 3)\n" +
            "   and t.jianyanstate = 0\n" +
            "   and t.state = 4\n" +
            "   and z.supporting_order_no = #{peitaodanhao, jdbcType = VARCHAR}\n")
    List<MesBzzBindDto> queryPtdBind(String supportingOrderNo);

//    @Update("update plan_goods_bind set yijiaodanhao = 'YJDH' || to_char(sysdate, 'yyyy-mm-dd') || lpad(seq_yjdh_code.nextval, 4, 0) where objuid in " +
//            "(select b.objuid from mes_bzz_ptd a, plan_goods_bind b where a.objuid = b.ptd_obj and a.state = 2 and b.jianyanstate = 0 and b.state = 0 " +
//            "and a.supporting_order_no = #{supportingOrderNo, jdbcType=VARCHAR})")
//    int updateBindYjdh(String supportingOrderNo);
//    @Update("update plan_goods_bind set bujiandanhao = 'BJDH' || to_char(sysdate, 'yyyy-mm-dd') || lpad(seq_bjdh_code.nextval, 4, 0) where objuid in " +
//            "(select b.objuid from mes_bzz_ptd a, plan_goods_bind b where a.objuid = b.ptd_obj and a.state = 3 and b.jianyanstate = 0 and b.state = 0 " +
//            "and a.supporting_order_no = #{supportingOrderNo, jdbcType=VARCHAR})")
//    int updateBindBjdh(String supportingOrderNo);
    @Update("update mes_bzz_ptd set state=3 where objuid in (select objuid from mes_bzz_ptd where supporting_order_no = #{supportingOrderNo, jdbcType=VARCHAR})" )
    int updatePtdStateZc(String supportingOrderNo);

    @Update("update plan_goods_bind " +
            "set plan_goods_bind.state = 1, " +
            "yijiaotime = sysdate," +
            "yijiaoren = #{objuid, jdbcType=VARCHAR}," +
            "yijiaodanhao = 'YJDH' || to_char(sysdate, 'yyyy-mm-dd') || lpad(seq_yjdh_code.nextval, 4, 0)" +
            "where plan_goods_bind.objuid in " +
            "(select b.objuid " +
            "from mes_bzz_ptd a, plan_goods_bind b " +
            "where a.objuid = b.ptd_obj " +
            "and a.state = 2 " +
            "and b.jianyanstate = 0 " +
            "and b.state = 4 " +
            "and a.supporting_order_no = #{supportingOrderNo, jdbcType=VARCHAR})")
    int updateBindStateZc(@Param("supportingOrderNo") String supportingOrderNo,@Param("objuid") String objuid);
    @Update("update plan_goods_bind " +
            "set plan_goods_bind.state = 2, " +
            "bujiandanhao = 'BJDH' || to_char(sysdate, 'yyyy-mm-dd') || lpad(seq_bjdh_code.nextval, 4, 0)" +
            "where plan_goods_bind.objuid in " +
            "(select b.objuid " +
            "from mes_bzz_ptd a, plan_goods_bind b " +
            "where a.objuid = b.ptd_obj " +
            "and a.state = 3 " +
            "and b.jianyanstate = 0 " +
            "and b.state = 4 " +
            "and a.supporting_order_no = #{supportingOrderNo, jdbcType=VARCHAR})")
    int updateBindStateBj(String supportingOrderNo);

    @Select("select b.*,a.quantity_of_missing_parts,a.name,a.proportion from mes_bzz_ptdmx a, plan_goods_bind b " +
            "where a.objuid = b.ptdmx_obj " +
            "and a.quantity_of_missing_parts != 0 " +
            "and b.peitaodanhao = #{peitaodanhao, jdbcType=VARCHAR}")
    List<MesBzzBindDto> queryPtdmxQjs(String peitaodanhao);


    // 生成转场保留单
    @Insert("insert into mes_bzz_zcbld" +
            "(objuid,baoliudanhao," +
            " model, jiaci, ptdid," +
            " yijiaodanwei, yijiaoren, jieshoudanwei, " +
            " jieshoudaibiao, factory_code, creat_name)" +
            "values" +
            "(#{objuid, jdbcType=VARCHAR},'BLDH' || to_char(sysdate, 'yyyy-mm-dd') || lpad(seq_bldh_code.nextval, 4, 0)," +
            "#{model, jdbcType=VARCHAR},#{jiaci, jdbcType=VARCHAR},#{ptdid, jdbcType=VARCHAR}," +
            "#{yijiaodanwei, jdbcType=VARCHAR},#{yijiaoren, jdbcType=VARCHAR},#{jieshoudanwei, jdbcType=VARCHAR}," +
            "#{jieshoudaibiao, jdbcType=VARCHAR},#{factoryCode, jdbcType=VARCHAR},#{creatName, jdbcType=VARCHAR})")
    int addMesBzzZcbld(MesBzzZcbldDto inFoDtoParam);
}
